# -*- coding: utf-8 -*-

###########################################################################
#
#   Copyright (c) 2020  Bresee, Inc. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License"
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
###########################################################################

"""
annual fee management
"""


import time
import utils.pgsql.base.pgconn as pgconn

_tblName = "tbl_annualfee"

#   ""
_DB_SCRIPT = """
CREATE TABLE IF NOT EXISTS {} (
                "annualfeeId"  SERIAL PRIMARY KEY ,    
                "caseId" INTEGER NOT NULL default 0,   
                "deviceId" INTEGER NOT NULL default 0,   
                "activation" INTEGER NOT NULL default 0,  
                "expirationDate" VARCHAR(32) NOT NULL default '',  
                "data" INTEGER NOT NULL default -1 ,   
                "auditatatus" VARCHAR(32) NOT NULL default 'none',  
                "equipmentFor" VARCHAR(32) NOT NULL default '', 
                "uniqueCode" VARCHAR(32) NOT NULL default '',  
                "createTime" VARCHAR(32) NOT NULL default '' ,  
                "updateTime" VARCHAR(32) NOT NULL default ''   
                );""".format(
    _tblName
)


class PgAnnualfee:
    def __init__(self):
        self._pgConn = pgconn.pgMainConn

    def __del__(self):
        pass

    def createTbl(self):
        """
        create table
        :return:
        """
        return self._pgConn.exec(_DB_SCRIPT)

 
    def add(self, dic) -> str:
        dic["createTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        sql_key = '\"' + '\", \"'.join(dic.keys()) + '\"'
        sql_val = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_val = sql_val + '\'' + dic[key] + '\','
            else:
                sql_val = sql_val + str(dic[key]) + ' ,'
     
        sql = f"""insert into {_tblName} ({sql_key}) values ({sql_val[:-1]}) RETURNING "{"annualfeeId"}" """
        return pgconn.pgMainConn.exec_fetchone(sql)


    def get_deviceNum(self, caseId) -> dict:
        sql = 'select  a.*,n."deviceName" from {}  a left Join tbl_device n on a."deviceId"=n."deviceId"  where a."caseId" = {} '.format(
            _tblName, caseId
        )
        return self._pgConn.exec_fetchall(sql)


    def get_unusedevice(self, caseId) -> dict:
        sql = (
            "select * from {} where \"caseId\" = '{}' and \"activation\" = '0'".format(
                _tblName, caseId
            )
        )
        return self._pgConn.exec_fetchall(sql)


    def get_all(self) -> dict:
        sql = "select * from {}  ".format(_tblName)
        return self._pgConn.exec_fetchall(sql)


    def get_annualfeeId(self, annualfeeId) -> dict:
        sql = "select * from {} where \"annualfeeId\" = '{}' ".format(
            _tblName, annualfeeId
        )
        return self._pgConn.exec_fetchone(sql)


    def get_auditatatus(self) -> dict:
        # sql = "select * from {} where \"auditatatus\" = '审核中' ".format(_tblName)
        sql = 'select a.*,b."caseName",n."deviceName"  from {} a left Join tbl_case b on a."caseId"=b."caseId"  left Join tbl_device n on a."deviceId"=n."deviceId"  where a."auditatatus" = \'审核中\' '.format(
            _tblName
        )
        return self._pgConn.exec_fetchall(sql)


    def get_data(self) -> dict:
        sql = 'select a.*,b."caseName" ,n."deviceName" from {} a left Join tbl_case b on a."caseId"=b."caseId"  left Join tbl_device n on a."deviceId"=n."deviceId" where a."data" < \'31\' order by "caseId" '.format(
            _tblName
        )
        return self._pgConn.exec_fetchall(sql)


    def get_deviceId(self, deviceId) -> dict:
        sql = "select * from {} where \"deviceId\" = '{}' ".format(_tblName, deviceId)
        return self._pgConn.exec_fetchone(sql)


    def modify(self, ids, dic, keys):
        dic["updateTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        sql_key = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_key = sql_key + '\"' + key + '\"' + ' = \'' + dic[key] + '\','
            else:
                sql_key = sql_key + '\"' + key + '\"' + ' = ' + str(dic[key]) + ' ,'


        sql =  "update {} set {} where  ".format(_tblName, sql_key[:-1])+ " \"{}\" = '{}' ".format(keys, ids)
        return self._pgConn.exec(sql)
